package com.safeneeds.linguimana.entity.dao.impl;

import java.util.List;

import org.apache.commons.net.nntp.NewsgroupInfo;
import org.hibernate.Query;

import com.safeneeds.linguimana.dto.DataStorePort;
import com.safeneeds.linguimana.dto.PortConstant;
import com.safeneeds.linguimana.entity.EntityPortNews;
import com.safeneeds.linguimana.entity.PortNews;
import com.safeneeds.linguimana.entity.PortNewsCate;
import com.safeneeds.linguimana.entity.dao.IPortNewsDao;
import com.safeneeds.util.base.BaseHibernateDao;
import com.safeneeds.util.date.DateUtil;
import com.safeneeds.util.other.StringUtil;
import com.safeneeds.util.page.PageInfo;

/**
 * 门户新闻
 * 
 * @author gaof
 * @date 2017年2月8日
 */
public class PortNewsDao extends BaseHibernateDao<PortNews, String> implements
		IPortNewsDao {

	/**
	 * 获取新闻分页数据
	 * 
	 * @param pageInfo
	 * @param dataStore
	 * @return
	 */
	@Override
	public PageInfo listNewsPage(PageInfo pageInfo, DataStorePort dataStore) {

		String hsql = "SELECT t2.NEWS_ID,t2.TITLE,t2.TAG,t2.UPDATE_USER,t2.UPDATE_TIME,t2.READ_COUNT,t2.IMAGE,t2.NEWS_STATUS,t3.name cate_name FROM PORT_NEWS_CATE_MAP t1";
		String countSql = "SELECT COUNT(*) FROM PORT_NEWS_CATE_MAP t1";
		String join = " INNER JOIN PORT_NEWS t2 on t2.NEWS_ID = t1.NEWS_ID"
				+ " INNER JOIN PORT_NEWS_CATE t3 ON t3.CATE_ID = t1.CATE_ID";
		String whereSql = "";
		String orderSql = " ORDER BY NVL(t2.STICK, 0) desc, t2.UPDATE_TIME desc";

		if (dataStore != null) {

			whereSql += " where 1 = 1";

			// 有分类
			if (!StringUtil.isEmpty(dataStore.getCatesSplice())) {
				whereSql += " and t1.CATE_ID in(" + dataStore.getCatesSplice()
						+ ")";
			}

			// 标题
			if (!StringUtil.isEmpty(dataStore.getTitle())) {
				whereSql += " and t2.TITLE like '%"
						+ StringUtil.addQuote(dataStore.getTitle()) + "%'";
			}

			// 分类
			if (!StringUtil.isEmpty(dataStore.getType())) {
				whereSql += " and t1.CATE_ID = '"
						+ StringUtil.addQuote(dataStore.getType()) + "'";
			}

			// 是否隐藏
			if (!StringUtil.isEmpty(dataStore.getStatus())) {
				whereSql += " and t2.NEWS_STATUS = '"
						+ StringUtil.addQuote(dataStore.getStatus()) + "'";
			}

			// 时间
			if (!StringUtil.isEmpty(dataStore.getStart())
					&& !StringUtil.isEmpty(dataStore.getEnd())) {// 有开始时间和结束时间
				if (DateUtil.compareDateYYYYMMDD(dataStore.getStart(),
						dataStore.getEnd())) {// 开始时间大于结束时间
					whereSql += " and UPDATE_TIME >= to_timestamp('"
							+ dataStore.getEnd()
							+ " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')"
							+ " and UPDATE_TIME <= to_timestamp('"
							+ dataStore.getStart()
							+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')";
				} else {
					whereSql += " and UPDATE_TIME <= to_timestamp('"
							+ dataStore.getEnd()
							+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')"
							+ " and UPDATE_TIME >= to_timestamp('"
							+ dataStore.getStart()
							+ " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')";
				}
			} else if (!StringUtil.isEmpty(dataStore.getStart())) {// 只有开始时间
				whereSql += " and UPDATE_TIME >= to_timestamp('"
						+ dataStore.getStart()
						+ " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')";
			} else if (!StringUtil.isEmpty(dataStore.getEnd())) {// 只有结束时间
				whereSql += " and UPDATE_TIME <= to_timestamp('"
						+ dataStore.getEnd()
						+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')";
			}
		}

		pageInfo.setEntityClass(EntityPortNews.class);
		pageInfo.setListHbSql(hsql + join + whereSql + orderSql);
		pageInfo.setTotalCountHbSQL(countSql + join + whereSql);

		return pageInfo;
	}

	/**
	 * 添加新闻
	 * 
	 * @param news
	 * @return
	 */
	@Override
	public PortNews addNews(PortNews news) {
		return this.saveEntity(news);
	}

	/**
	 * 通过新闻id获取新闻
	 * 
	 * @param newsId
	 * @return
	 */
	@Override
	public PortNews getNewsById(String newsId) {
		return this.findById(newsId);
	}

	/**
	 * 获取所有带图片的新闻
	 */
	@Override
	public List<PortNews> getNewExistImage() {
		return this
				.getEntitiesPage(
						"from PortNews where NEWS_STATUS = 1 AND IMAGE IS NOT NULL ORDER BY UPDATE_TIME",
						1, 6);
	}

	/**
	 * 获取置顶新闻 更新日期最新的
	 */
	@Override
	public PortNews getTopNews() {
		if("0".equals((this.getSQLQuery("select count(*) from Port_News where NEWS_STATUS = 1 AND STICK='"+PortConstant.NEWS_STATUS_TOP+"' ORDER BY UPDATE_TIME").get(0)+""))){
			return this.getEntity("from PortNews where NEWS_STATUS = 1 ORDER BY UPDATE_TIME desc");
			
		}else{
			return this.getEntity("from PortNews where NEWS_STATUS = 1 AND STICK='"+PortConstant.NEWS_STATUS_TOP+"' ORDER BY UPDATE_TIME desc");
		}

	}

	@Override
	public List<PortNews> getNewNews() {
		String topId=getTopNews().getNewsId();
		return this.getEntitiesPage("from PortNews where NEWS_STATUS = 1 and NEWS_ID <> '"+topId+"' ORDER BY UPDATE_TIME desc", 1, 4);
		
	}

	/**
	 * 通过分类Id获取分类新闻
	 * 
	 * @param cateId
	 * @param i
	 * @return
	 */
	@Override
	public List<PortNews> getCateNews(String cateId, int i) {
		String sql = "select * from(select n.* from port_news n ,port_news_cate_map m where n.news_id=m.news_id and m.cate_id='"
				+ cateId + "' order by update_time ) where rownum <= " + i;
		List<PortNews> list = this.getSQLQuery(sql, PortNews.class);

		return list;
	}

	@Override
	public List<PortNews> getSearchNews(String key) {
		String sql = "select * from port_news where instr(title,'" + key
				+ "')>0 or instr(content,'" + key
				+ "')>0 or instr(description,'" + key + "')>0";
		List<PortNews> list = this.getSQLQuery(sql, PortNews.class);

		return list;
	}

	public int updateNewShowDao(String ids, String isShow) {
		// TODO Auto-generated method stub
		String sql = "update PortNews set news_status = '" + isShow
				+ "' where news_id = '" + ids + "'";
		return this.currentSession().createQuery(sql).executeUpdate();

	}
	/**
	 * 获取name分类新闻的前i条
	 * 
	 * @param name i
	 * @return
	 */
	@Override
	public List<PortNews> getCateNews(int i, String name) {
		String hsql="SELECT pn.* from PORT_NEWS pn, PORT_NEWS_CATE_MAP pnm "
				+ "WHERE NEWS_STATUS='1' and PN.NEWS_ID=PNM.NEWS_ID AND PNM.CATE_ID IN ( "
				+ "SELECT pnc1.CATE_ID FROM PORT_NEWS_CATE pnc1, PORT_NEWS_CATE pnc2 "
				+ "WHERE PNC1.PID = PNC2.CATE_ID AND PNC2.name='"+name+"' AND PNC2.pid='0' ) "
				+ "ORDER BY PN.STICK DESC,PN.UPDATE_TIME DESC";
		return this.getSQLQuery(hsql, PortNews.class);
	}

}
